-- @author lixiyong01
-- @date 2023.03.01
-- 整合对外的宽表层流量明细层

CREATE TABLE `hdp_lbg_supin_zplisting.app_zp_wb_n_flow`(
    `stime` string COMMENT '检索时间，yyyyMMddHHmmss',
    `sid` string COMMENT '检索id 唯一检索标识',
    `pid` string COMMENT '页面id，可以解析页面时间',
    `platform` int COMMENT '平台类型,1-pc，2-m，3-app， 4-小程序， 5-wap',
    `requesttype` string COMMENT '请求商业类型',
    `requesttype_name` string COMMENT '请求商业类型name',
    `slot` string COMMENT '推荐位标识',
    `slot_name` string COMMENT '推荐位简称',
    `source1` string COMMENT '流量来源一级',
    `source2` string COMMENT '流量来源二级',
    `source3` string COMMENT '流量来源三级',
    `logintag` int COMMENT '用户是否登录：是1，否0',
    `userid` bigint COMMENT '用户id',
    `ukey` string COMMENT '用户唯一标识',
    `cookieid` string COMMENT '用户cookie，取自id58',
    `imei` string COMMENT '用户设备号',
    `resumeid` bigint COMMENT '简历id',
    `info_id` bigint COMMENT '帖子id',
    `info_buid` bigint COMMENT '帖子发布buid，客户账号user_id',
    `info_buid_type` int COMMENT '帖子发布用户类型',
    `info_etpid` bigint COMMENT '帖子对应企业id（不唯一），统一使用info_etpname',
    `info_etpname` bigint COMMENT '帖子对应企业名称',
    `info_title` string COMMENT '帖子标题',
    `info_cate1` string COMMENT '职位一级类别',
    `info_cate2` string COMMENT '职位二级类别',
    `info_cate3` string COMMENT '职位三级类别',
    `info_cate2_name` string COMMENT '职位二级类别name',
    `info_cate3_name` string COMMENT '职位三级类别name',
    `info_local1` string COMMENT '职位一级发布地域',
    `info_local2` string COMMENT '职位二级发布地域',
    `info_local3` string COMMENT '职位三级发布地域',
    `info_local1_name` string COMMENT '职位一级发布地域name',
    `info_local2_name` string COMMENT '职位二级发布地域name',
    `info_type` string COMMENT '检索时帖子命中的商业类型',
    `info_types2` string COMMENT '帖子当前所有商业类型，逗号分割',
    `page_no` int COMMENT '检索页码',
    `page_pos` int COMMENT '帖子绝对位置序号',
    `has_exposure` int COMMENT '是否有曝光：是1，否0',
    `exposure_cnt` bigint COMMENT '曝光次数',
    `exposure_times2` bigint COMMENT '曝光总时长（当天内）',
    `has_infodetail` int COMMENT '是否有点击：是1，否0',
    `info_detail_cnt` bigint COMMENT '点击次数',
    `has_im` int COMMENT '是否有微聊：是1，否0',
    `bc_type` string COMMENT '会话发起方bc判定，value = b，c',
    `has_ack` int COMMENT '会话中首条消息是否被接收',
    `has_read` int COMMENT '会话中首条消息是否被阅读',
    `has_reply` int COMMENT '首条消息是否被回复',
    `has_delivery` int COMMENT '是否有投递：是1，否0',
    `delivery_cnt` bigint COMMENT '投递数，分享场景会n次投递',
    `has_tel` int COMMENT '是否有电话沟通：是1，否0',
    `tel_cnt` bigint COMMENT '电话沟通次数',
    `tel_conn_cnt` bigint COMMENT '电话接通次数',
    `tel_conn_times2` bigint COMMENT '总电话接通时长',
    `has_yx` int COMMENT '是否有优选：是1，否0',
    `yx_cost` bigint COMMENT '优选扣费总金额（万分之一元）',
    `yx_cash` bigint COMMENT '优选扣费现金（万分之一元）',
    `has_jz` int COMMENT '是否有精准：是1，否0',
    `jz_cost` bigint COMMENT '黄金展位扣费总金额（万分之一元）',
    `jz_cash` bigint COMMENT '黄金展位扣费现金（万分之一元）'
)
COMMENT '58招聘行为串联扩展表'
PARTITIONED BY (`dt` string COMMENT '日期分区')
STORED AS orc


select a.info_local1, b.areaname,
  uv, buid_cnt, info_cnt, exp_cnt, click_cnt, im_cnt2, tel_cnt2, delivery_cnt2
from (
    select info_local1,
        count(distinct ukey) as uv,
        count(distinct buid) as buid_cnt,
        count(distinct info_id) as info_cnt,
        sum(has_exposure) as exp_cnt,
        sum(has_infodetail) as click_cnt,
        sum(if(bc_type='c', has_im, 0)) as im_cnt2,
        sum(if(tel_conn_cnt>0, has_tel, 0)) as tel_cnt2,
        sum(has_delivery) as delivery_cnt2
    from hdp_lbg_supin_zplisting.app_zp_gj_n_flow
    where dt=20230223 and info_cate3 in (
        select cateid
        from hdp_58_common_defaultdb.ds_dict_cmc_category
        where statdate='20230223' and catename='送餐员' and cateids[0]='574'
    )
    group by info_local1
) a
inner join (
    select areaid,areaname,depth,areaids
    from hdp_58_common_defaultdb.ds_dict_cmc_displocal
    where statdate='20230223' and depth=0
) b on a.info_local1=b.areaid



left join
    hdp_58_common_defaultdb.ds_dict_cmc_category b
    on a.info_local1 = b.local1

left join
    hdp_58_common_defaultdb.ds_dict_cmc_displocal c
